package taskmaster.util;

import taskmaster.auth.User;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;


/**
 * taskmaster.util.LookupLists
 * <BR><BR>
 * LookupLists provides an abstraction layer between the calling class and the database.<BR>
 * This hides the actual database query details and puts the data in either arrays or hashes as is convenient for the externally callable methods.
 * <BR><BR>
 * Used throughout a bunch of portions throughout taskmaster.
 * <BR><BR>
 * <!--Original nostalgic use...: A lookup to match roleId's with Role names.-->
 */
public class LookupLists extends taskmaster.util.RunStatement
{

    /**
     * instantiate this and set its owner.
     */
    public LookupLists(User user) throws Exception
    {
        this.user = user;
        this.instantiateLists();
    }

    private final User user;
    private String[][] allRolesArray = null;
    private String[][] usersRolesArray = null;
    private Hashtable<Integer,String> allRoles = null;
    private String[][] allStatusesArray = null;
    private Hashtable allStatuses = null;
    private Hashtable allStatusesComplete = null;
    private String[][] allTypesArray = null;
    private Hashtable allTypes = null;
    private Hashtable allTypesNeedUser = null;
    private String[] allJobs = null;
    private String[] usersJobs = null;
    private String[][] allAllowedActions = null;

    /**
     * instantiates all internal data members; called upon instantiation of the class
     */
    private void instantiateLists() throws Exception
    {
        this.allRolesArray = this.getAllRoles();
        this.usersRolesArray = this.getUsersRoles();
        this.allRoles = this.arrayToHash(this.allRolesArray, 0, 1);
        this.allStatusesArray = this.getAllStatuses();    // make this a local array, not needed (?)
        this.allStatuses = this.arrayToHash(this.allStatusesArray, 0, 1);
        this.allStatusesComplete = this.arrayToHash(this.allStatusesArray, 0, 2);
        this.allTypesArray = this.getAllTypes();
        this.allTypes = this.arrayToHash(allTypesArray, 0, 1);
        this.allTypesNeedUser = this.arrayToHash(allTypesArray, 0, 2);
        this.allJobs = this.getAllJobs();
        this.usersJobs = this.getUsersJobs();
        this.allAllowedActions = this.getAllAllowedActions();
    }

    /**
     * generic method to turn an array into a hash using key as row in the array to be used as the hashkey and value as the value in the array to be used as the value in the hash
     */
    private Hashtable arrayToHash(String[][] array, int key, int value)
    {
        if (array == null)
        {
            return null;
        }
        Hashtable hash = new Hashtable((int) (array.length / .75) + 1, (float) .75);
        for (int i = 0; i < array.length; i++)
        {
            hash.put(array[i][key], array[i][value]);
        }
        return hash;
    }

    /**
     * generic method to execute a preparedstatement and return an array of its values
     *
     * @param pstmt      the prepared statement to be executed
     * @param intValue   the value to be used as the internal value (like status id)
     * @param extValue   the value to be used as the external value (like status name)
     * @param arrayWidth the width of the array to be returned.  anything > 2 will result in uninitialized values for those columns. <2 will result in an exception.
     */
    private String[][] getListArray(PreparedStatement pstmt, String intValue, String extValue, int arrayWidth) throws SQLException
    {
        ResultSet rs = pstmt.executeQuery();
        rs.last();
        int rsSize = rs.getRow();
        rs.beforeFirst();
        String[][] result = new String[rsSize][arrayWidth];

        for (int i = 0; i < result.length && rs.next(); i++)
        {
            result[i][0] = rs.getString(intValue);
            result[i][1] = rs.getString(extValue);
        }
        rs.close();
        return result;
    }

    /**
     * get all the statuses that are in the complete category
     */
    public Hashtable getAllStatusesComplete()
    {
        return this.allStatusesComplete;
    }

    /**
     * determines the category of the task, based on its statusId and task type.
     * <BR><BR>
     * <I>the whole logic of this could probably be put directly into the sql...</I>
     */
    public String determineCategory(int statusId, char type)
    {
        String isCompleteString = (String) this.allStatusesComplete.get(String.valueOf(statusId));
        if (isCompleteString.equalsIgnoreCase("completed") ||
                isCompleteString.equalsIgnoreCase("running") ||
                isCompleteString.equalsIgnoreCase("error"))
        {
            return isCompleteString;
        }
        if (isCompleteString.equalsIgnoreCase("notYetRun"))
        {
            if ('C' == type)
            {
                return "cron";
            }
            String needsUserString = (String) this.allTypesNeedUser.get(type + "");
            if ("1".equals(needsUserString))
            {
                return "needsUser";
            }
        }
        return "blocked";
    }

    /**
     * determines the ordering of categories on the page.
     * <BR><BR>categories are currently ordered by:<BR>
     * - needsUser<BR>
     * - running<BR>
     * - error<BR>
     * - cron<BR>
     * - blocked<BR>
     * - completed<BR>
     * - rest (shouldn't be any left over)
     * <BR><BR>
     * At this time, crons & completed are grouped into the last category by the jsp page
     * since crons are never tasks but only dependencies of tasks
     */
    public Integer determineCatOrdering(String category)
    {
        if (category.equals("needsUser"))
        {
            return new Integer(1);
        }
        if (category.equals("running"))
        {
            return new Integer(3);
        }
        if (category.equals("error"))
        {
            return new Integer(4);
        }
        if (category.equals("cron"))
        {
            return new Integer(5);
        }
        if (category.equals("blocked"))
        {
            return new Integer(6);
        }
        if (category.equals("completed"))
        {
            return new Integer(7);
        }
        return new Integer(8);
    }

    private PreparedStatement pstmtAllRoles = null;

    /**
     * get a list of all roles in tms.roles
     */
    public String[][] getAllRoles() throws SQLException
    {
        if (this.allRolesArray != null)
        {
            return this.allRolesArray;
        }
        if (this.pstmtAllRoles == null)
        {
            this.pstmtAllRoles = this.getPreparedStatement(user, "select ROLE_ID, ROLE_NAME from tms.role_names order by ROLE_NAME asc");
        }
        String[][] roles = this.getListArray(this.pstmtAllRoles, "ROLE_ID", "ROLE_NAME", 3);
        for (int i = 0; i < roles.length; i++)
        {
            roles[i][2] = user.isInRole(roles[i][0]);
        }
        return roles;
    }

    private PreparedStatement pstmtUsersRoles = null;

    /**
     * get a list of all roles limited to the roles that the user has read, write or admin access.
     */
    public String[][] getUsersRoles() throws Exception
    {
        if (this.usersRolesArray != null)
        {
            return this.usersRolesArray;
        }
        if (this.pstmtUsersRoles == null)
        {
            this.pstmtUsersRoles = this.getPreparedStatement(user, "select r.ROLE_ID, r.ROLE_NAME, u.ACCESS_LEVEL from tms.role_names r, tms.USER_ROLES u where u.ROLE_ID = r.ROLE_ID AND username = ? order by ROLE_NAME asc");
        }
        this.pstmtUsersRoles.setString(1, this.user.getUsername());
        ResultSet rs = this.pstmtUsersRoles.executeQuery();
        rs.last();
        int rsSize = rs.getRow();
        rs.beforeFirst();
        String[][] roles = new String[rsSize][3];

        for (int i = 0; i < roles.length && rs.next(); i++)
        {
            roles[i][0] = rs.getString("ROLE_ID");
            roles[i][1] = rs.getString("ROLE_NAME");
            roles[i][2] = rs.getString("ACCESS_LEVEL");
        }
        rs.close();
        return roles;
    }

    /**
     * get a hashtable of all roles in the tms system
     */
    public Hashtable getAllRolesHash()
    { return this.allRoles; }

    private PreparedStatement pstmtAllStatuses = null;

    /**
     * returns a list of all status_id / status names in the db
     */
    public String[][] getAllStatuses() throws Exception
    {
        if (this.allStatusesArray != null)
        {
            return this.allStatusesArray;
        }
        if (this.pstmtAllStatuses == null)
        {
            this.pstmtAllStatuses = this.getPreparedStatement(user, "select STATUS_ID, DISPLAY, CATEGORY from tms.task_statuses order by DISPLAY asc");
        }
        ResultSet rs = this.pstmtAllStatuses.executeQuery();
        rs.last();
        int rsSize = rs.getRow();
        rs.beforeFirst();
        String[][] result = new String[rsSize][4];

        for (int i = 0; i < result.length && rs.next(); i++)
        {
            result[i][0] = rs.getString("STATUS_ID");
            result[i][1] = rs.getString("DISPLAY");
            result[i][2] = rs.getString("CATEGORY");
        }
        rs.close();
        return result;
    }

    /**
     * get a hashtable of all the possible statuses in the tms system
     */
    public Hashtable getAllStatusesHash()
    { return this.allStatuses; }

    private PreparedStatement pstmtAllTypes = null;

    /**
     * returns a list of all task_type/description pairs in the db
     */
    public String[][] getAllTypes() throws Exception
    {
        if (this.allTypesArray != null)
        {
            return this.allTypesArray;
        }
        if (this.pstmtAllTypes == null)
        {
            this.pstmtAllTypes = this.getPreparedStatement(user, "select TASK_TYPE, DESCRIPTION, NEEDS_USER from tms.task_types order by DESCRIPTION asc");
        }
        ResultSet rs = this.pstmtAllTypes.executeQuery();
        rs.last();
        int rsSize = rs.getRow();
        rs.beforeFirst();
        String[][] result = new String[rsSize][3];

        for (int i = 0; i < result.length && rs.next(); i++)
        {
            result[i][0] = rs.getString("TASK_TYPE");
            result[i][1] = rs.getString("DESCRIPTION");
            result[i][2] = rs.getString("NEEDS_USER");// used when determining category of Task
        }
        rs.close();
        return result;
    }

    /**
     * return a hashtable of all the possible task types
     */
    public Hashtable getAllTypesHash()
    { return this.allTypes; }

    private PreparedStatement pstmtAllJobs = null;

    /**
     * get a list of all jobs in tms.jobs as a single dimensional array.
     */
    public String[] getAllJobs() throws Exception
    {
        if (this.allJobs != null)
        {
            return this.allJobs;
        }
        if (this.pstmtAllJobs == null)
        {
            this.pstmtAllJobs = this.getPreparedStatement(user, "select unique(j.JOB_TAG) from tms.jobs j");
        }
        ResultSet rs = this.pstmtAllJobs.executeQuery();
        rs.last();
        int rsSize = rs.getRow();
        rs.beforeFirst();
        this.allJobs = new String[rsSize];

        for (int i = 0; i < this.allJobs.length && rs.next(); i++)
        {
            this.allJobs[i] = rs.getString("JOB_TAG");
        }
        return allJobs;
    }

    private PreparedStatement pstmtUsersJobs = null;

    /**
     * get a list of all jobs in single dimensional array in which this user has at least one writable task.
     */
    public String[] getUsersJobs() throws Exception
    {
        if (this.usersJobs != null)
        {
            return this.usersJobs;
        }
        if (this.pstmtUsersJobs == null)
        {
            this.pstmtUsersJobs = this.getPreparedStatement(user, "select unique(j.JOB_TAG) from tms.jobs j, tms.tasks t, tms.user_roles u where j.JOB_TAG = t.JOB_TAG AND u.ROLE_ID = t.ROLE_ID AND u.USERNAME = ? order by j.JOB_TAG");
        }
        this.pstmtUsersJobs.setString(1, this.user.getUsername());
        ResultSet rs = this.pstmtUsersJobs.executeQuery();
        rs.last();
        int rsSize = rs.getRow();
        rs.beforeFirst();
        this.usersJobs = new String[rsSize];

        for (int i = 0; i < this.usersJobs.length && rs.next(); i++)
        {
            this.usersJobs[i] = rs.getString("JOB_TAG");
        }
        return this.usersJobs;
    }

    private PreparedStatement pstmtAllAllowedActions = null;

    /**
     * get a 2d array of all actions mapped to taskType/status.
     * <BR><BR>More efficient than getAllowedActions() when looking up a whole bunch of Tasks consequtively,
     * but requires a search through the array as implemented in ViewTasks
     */
    public String[][] getAllAllowedActions() throws SQLException
    {
        if (this.allAllowedActions != null)
        {
            return this.allAllowedActions;
        }
        if (this.pstmtAllAllowedActions == null)
        {
            String sql = "select t.STATUS_ID, t.TASK_TYPE, t.TO_STATUS, t.DISPLAY, t.NEEDS_ADMIN, t.IS_RECURSIVE from tms.actions t";
            this.pstmtAllAllowedActions = this.getPreparedStatement(user, sql);
        }
        ResultSet rs = this.pstmtAllAllowedActions.executeQuery();
        rs.last();
        int rsSize = rs.getRow();
        rs.beforeFirst();
        String[][] jobs = new String[rsSize][6];

        for (int i = 0; i < jobs.length && rs.next(); i++)
        {
            jobs[i][0] = rs.getString("STATUS_ID");
            jobs[i][1] = rs.getString("TO_STATUS");
            jobs[i][2] = rs.getString("TASK_TYPE");
            jobs[i][3] = rs.getString("DISPLAY");
            jobs[i][4] = rs.getString("NEEDS_ADMIN");
            jobs[i][5] = rs.getString("IS_RECURSIVE");
        }
        return jobs;
    }

    private PreparedStatement pstmtSysdate = null;

    /**
     * get oracle time.  This is sysdate according to the database that the user is connected to at the time of the call to this method.
     */
    public java.util.Date getOracleTime() throws Exception
    {
        if (this.pstmtSysdate == null)
        {
            this.pstmtSysdate = this.getPreparedStatement(user, "SELECT SYSDATE NOW FROM DUAL");
        }// from oracle docs
        ResultSet rs = this.pstmtSysdate.executeQuery();
        rs.next();
        return new java.util.Date(rs.getTime("NOW").getTime());
    }

    public HashMap<String,Integer > getreverseUserRolesHash() throws Exception {

      String[][] usersRoles =getUsersRoles();
      HashMap<String, Integer> userRoleNames=new HashMap<String, Integer>();
        for (int i = 0; i < usersRoles .length; i++)
        {


            userRoleNames.put(usersRoles[i][1], Integer.valueOf(usersRoles[i][0]));

        }

        return userRoleNames;


    }


}